'''
Author: your name
Date: 2021-03-23 09:41:58
LastEditTime: 2021-03-30 17:12:33
LastEditors: Please set LastEditors
Description: In User Settings Edit
FilePath: /tinyms/tinyms-api/apps/auth/census/service.py
'''
import json
from app import db

class CensusService():
    def census_organ():
        # sql="""
        #     SELECT a.dict_name AS qhmc,a.dict_code AS qhdm,a.dict_level AS qhjb,count(b.organ_pk) AS yhNum FROM (
        #     SELECT DISTINCT dict_code,(substr(dict_name,0,2) || substr(dict_name,length(dict_name),1)) AS dict_name,dict_level FROM sys_dict WHERE dict_type='XZQH' AND dict_status='1') a 
        #     LEFT JOIN (SELECT*FROM sys_organ WHERE organ_status='1') b ON a.dict_code=b.organ_code GROUP BY a.dict_code,a.dict_name,a.dict_level ORDER BY a.dict_code ASC
        #     """
        sql = """
            SELECT a.code,a.name,count(*) AS count from (SELECT substr(organ_code,1,4) AS code,organ_name AS name FROM sys_organ WHERE organ_parent='1') a 
            LEFT JOIN (SELECT SUBSTR(organ_code,1,4) AS code,organ_name AS name FROM sys_organ) b ON a.code=b.code GROUP BY code,name
            """
        res = db.session.execute(sql)
        all_res_list = res.fetchall()
        return all_res_list